<?php
class Reportes_model extends CI_Model {
    
	//constructor de la clase
    function __construct() {
        //LLamar al constructor del Modelo
        parent::__construct();
    }
	
	function secciones($id_seccion=NULL)
	{

       $where="";
        if ($id_seccion!=NULL) {
            $where=" WHERE id_seccion = $id_seccion ";
        }

		$sentencia="SELECT
                id_seccion,
                lower(nombre_seccion) AS seccion
            FROM
                org_seccion $where";
		$query=$this->db->query($sentencia);
        
        if ($id_seccion!=NULL) {
            return (array)$query->row();
        }


        return $query->result_array();
    
    }   
    function capacitaciones_por_seccion($info)
    {

        extract($info);
        $where="";
        if ($seccion!=0 && $seccion!="") {
            $where.=" AND id_seccion = $seccion ";
        }
         if ($area!=0 && $area!="") {
            $where.=" AND a.id_area = $area ";
        }

        $q="SELECT
                id_capacitacion,
                c.nombre AS capacitacion,
                a.area,
                COUNT(id_capacitacion) AS cant
            FROM
                edp_cap_solicitada
            INNER JOIN edp_capacitacion c USING(id_capacitacion) 
            INNER JOIN edp_area a USING(id_area)
            INNER JOIN edp_asignacion USING (id_evaluacion)
            INNER JOIN tcm_empleado e USING (id_empleado)
            WHERE  id_edicion = $id_edicion $where
            GROUP BY
                id_capacitacion 
                ORDER BY cant DESC
                LIMIT 10";

        $query=$this->db->query($q);
        return $query->result_array();

    }
    function por_niveles($info, $opcion=1) //primer reporte y octavo reporte
    {

        extract($info);


        $where="";

        if ($genero!=0) {
            $where=" AND id_genero = $genero";
        }
        if ($opcion==2) {
            $where.=" AND id_categoria <=2";
        }

        $q="SELECT
                    categoria AS label,
                    GROUP_CONCAT( genero ORDER BY genero, categoria ) as label2,
                    CONCAT('[',GROUP_CONCAT(    nota ORDER BY genero,categoria ),']') AS valor,
                    GROUP_CONCAT(   cant ORDER BY genero,categoria ) AS cant
                FROM
                    (
                        SELECT
                            c.categoria ,
                            ROUND(AVG(nota),2) AS nota,
                            e.genero,
                            e.id_genero,
                            COUNT(*) AS cant
                        FROM
                            edp_resultado_empleado e
                        INNER JOIN edp_categoria c ON c.id_categoria = e.nivel
                            WHERE id_edicion = $id_edicion $where
                        GROUP BY    nivel, id_genero
                    ) AS t
                GROUP BY
                    categoria;";

            $query=$this->db->query($q);
        return $query->result_array();
    }
        function por_superiores($info)//////////////////////////////segundo reporte
    {

       extract($info);
        $where="";

        if ($genero!=0) {
            $where=" AND r.id_genero = $genero";
        }
        $q="SELECT
                UPPER(seccion) AS label,
                GROUP_CONCAT(genero ORDER BY genero) as label2,
                CONCAT('[',GROUP_CONCAT(nota ORDER BY genero),']') AS valor,
                GROUP_CONCAT(cant ORDER BY genero) AS cant
            FROM
                (
                    SELECT
                        ROUND(AVG(nota),2) AS nota,
                        r.genero,
                        t.seccion,
                        COUNT(*) AS cant
                    FROM
                        edp_resultado_empleado r
                        INNER JOIN tcm_empleado t ON r.id_empleado_evaluador= t.id_empleado
                    WHERE id_edicion = $id_edicion AND 
                        t.id_seccion IN (34, 35, 40) $where
                    GROUP BY
                        t.id_seccion, r.genero
                ) AS t
            GROUP BY seccion";
        
        $query=$this->db->query($q);
        return $query->result_array(); 

    }
         function por_oficina($info)  ////////rerportes: tercero, cuarto 
    {

       extract($info);
        $where="";

        if ($genero!=0) {
            $where=" AND r.id_genero = $genero";
        }
        $q="SELECT
                UPPER(seccion) AS label,
                GROUP_CONCAT(genero ORDER BY genero) as label2,
                CONCAT('[',GROUP_CONCAT(nota ORDER BY genero),']') AS valor,
                GROUP_CONCAT(cant ORDER BY genero) AS cant
            FROM
                (
                    SELECT
                        ROUND(AVG(nota),2) AS nota,
                        r.genero,
                        r.seccion,
                        COUNT(*) AS cant
                    FROM
                        edp_resultado_empleado r
                    WHERE id_edicion = $id_edicion AND 
                        r.id_seccion IN ($id_seccion) $where
                    GROUP BY
                        id_seccion, r.genero
                ) AS t
            GROUP BY seccion";
        
        $query=$this->db->query($q);
        return $query->result_array(); 

    }

    function con_subordinados($info)
    {
        extract($info);
        $where="";

        if ($genero!=0) {
            $where=" AND id_genero = $genero";
        }

        $i=0;
        $q="SELECT                 
                seccion AS label,
                GROUP_CONCAT(genero ORDER BY genero) as label2,
                CONCAT('[',GROUP_CONCAT(nota ORDER BY genero),']') AS valor,
                GROUP_CONCAT(cant ORDER BY genero) AS cant
                 FROM (         ";
                    foreach ($id_seccion as $key) {

                            $q.=($i!=0)?" UNION ":"";                    

                            $q.="SELECT
                                (SELECT nombre_seccion FROM org_seccion WHERE id_seccion= $key) as seccion,
                                ROUND(AVG(nota),2) AS nota,
                                genero as genero,
                                COUNT(*) AS cant,
                                id_genero,
                                id_edicion
                            FROM
                                edp_resultado_empleado 
                            WHERE id_seccion = $key OR id_padre=$key OR id_abuelo=$key OR id_bisabuelo=$key
                            GROUP BY id_genero
                            ";    $i++;
                        }

                $q.=") AS t   WHERE id_edicion =$id_edicion $where 
                   GROUP BY seccion";
        
        $query=$this->db->query($q);
        return $query->result_array(); 

    }

     function razones($info)  ////////rerportes: tercero, cuarto 
    {

  
     extract($info);
        $where="";
        if ($seccion!=0 && $seccion!="") {
            $where.=" WHERE id_seccion = $seccion ";
        }

        $q="SELECT
                r.descripcion as razon, COUNT(*) as cant
            FROM
                edp_asignacion
            INNER JOIN edp_razon r USING (id_razon)
            INNER JOIN tcm_empleado t USING(id_empleado)
               $where
            GROUP BY id_razon";
        
        $query=$this->db->query($q);
        return $query->result_array();       
        
    }

    function reporte_niveles_seccion($id_edicion=NULL)
    {
        
     $q="SELECT
            seccion,
            GROUP_CONCAT(nota ORDER BY nivel SEPARATOR '**') as nota,
            GROUP_CONCAT(nivel ORDER BY nivel SEPARATOR '**') as categoria,
            GROUP_CONCAT(cant ORDER BY nivel SEPARATOR '**') as cant
        FROM
            (
                SELECT
                    ROUND(AVG(nota),2) AS nota,
                    seccion,
                    nivel,
            -- categoria,
                    COUNT(*) AS cant            
                FROM
                    edp_resultado_empleado
                WHERE id_edicion = $id_edicion
                GROUP BY
                    seccion,
                    nivel
            ) AS t
        GROUP BY
            seccion";
        
        $query=$this->db->query($q);
        return $query->result_array(); 
    }

function categorias() 
    {

        $q="SELECT * FROM edp_categoria";
        
        $query=$this->db->query($q);
        return $query->result_array();       
        
    }
}
?>